import java.sql.*;
import java.util.*;

public class DbComparator {

    public static void main(String[] args) {
        // 数据库1和数据库2的连接信息
        Db db1 = new Db("db1", "jdbc:mysql://localhost:3306/db1", "db1", "root1");
        Db db2 = new Db("db2", "jdbc:mysql://localhost:3306/db2", "db2", "root2");
        DbComparator dbComparator = new DbComparator();
        try {
            CompareResult compare = dbComparator.compare(db1, db2);
            System.out.println(compare);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public CompareResult compare(Db db1, Db db2) throws SQLException {
        CompareResult compareResult = new CompareResult();
        compareResult.setColumnDiffs(compareColumns(db1, db2));
        compareResult.setTableDiffs(compareTables(db1, db2));
        return compareResult;
    }

    private List<TableDiff> compareTables(Db db1, Db db2) throws SQLException {
        List<TableDiff> tableDiffs = new ArrayList<>();
        Set<String> tablesDb1 = getTables(db1);
        Set<String> tablesDb2 = getTables(db2);
        System.out.println("tablesDb1:" + tablesDb1);
        System.out.println("tablesDb2:" + tablesDb2);

        for (String table : tablesDb1) {
            if (tablesDb2.contains(table)) {
                if (areTablesEqual(db1, db2, table)) {
                    tableDiffs.add(new TableDiff(table, 1));
                } else {
                    tableDiffs.add(new TableDiff(table, 2));
                }
            } else {
                tableDiffs.add(new TableDiff(table, 3));
            }
        }

        for (String table : tablesDb2) {
            if (!tablesDb1.contains(table)) {
                tableDiffs.add(new TableDiff(table, 4));
            }
        }

        return tableDiffs;
    }

    private List<ColumnDiff> compareColumns(Db db1, Db db2) throws SQLException {
        List<ColumnDiff> columnDiffs = new ArrayList<>();
        Set<String> tablesDb1 = getTables(db1);

        for (String table : tablesDb1) {
            if (tableExists(db2, table)) {
                List<Column> columnsDb1 = getColumns(db1, table);
                List<Column> columnsDb2 = getColumns(db2, table);

                for (Column columnDb1 : columnsDb1) {
                    Column columnDb2 = findColumn(columnsDb2, columnDb1.getName());
                    if (columnDb2 != null) {
                        if (areColumnsEqual(columnDb1, columnDb2)) {
                            columnDiffs.add(new ColumnDiff(table, columnDb1.getName(), 1));
                        } else {
                            columnDiffs.add(new ColumnDiff(table, columnDb1.getName(), 2));
                        }
                    } else {
                        columnDiffs.add(new ColumnDiff(table, columnDb1.getName(), 3));
                    }
                }

                for (Column columnDb2 : columnsDb2) {
                    if (findColumn(columnsDb1, columnDb2.getName()) == null) {
                        columnDiffs.add(new ColumnDiff(table, columnDb2.getName(), 4));
                    }
                }
            }
        }

        return columnDiffs;
    }

    /**
     * 获取表
     * @param db
     * @return
     * @throws SQLException
     */
    private Set<String> getTables(Db db) throws SQLException {
        Set<String> tables = new HashSet<>();
        try (Connection conn = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPassword());
             ResultSet rs = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE"})) {
            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME"));
            }
        }
        return tables;
    }

    private boolean tableExists(Db db, String tableName) throws SQLException {
        try (Connection conn = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPassword());
             ResultSet rs = conn.getMetaData().getTables(null, null, tableName, new String[]{"TABLE"})) {
            return rs.next();
        }
    }

    private List<Column> getColumns(Db db, String tableName) throws SQLException {
        List<Column> columns = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(db.getUrl(), db.getUsername(), db.getPassword());
             ResultSet rs = conn.getMetaData().getColumns(null, null, tableName, "%")) {
            while (rs.next()) {
                Column column = new Column();
                column.setName(rs.getString("COLUMN_NAME"));
                column.setType(rs.getString("TYPE_NAME"));
                column.setLength(rs.getInt("COLUMN_SIZE"));
                column.setNullable(rs.getBoolean("IS_NULLABLE"));
                columns.add(column);
            }
        }
        return columns;
    }

    private boolean areTablesEqual(Db db1, Db db2, String tableName) throws SQLException {
        List<Column> columnsDb1 = getColumns(db1, tableName);
        List<Column> columnsDb2 = getColumns(db2, tableName);
        return columnsDb1.equals(columnsDb2);
    }

    private boolean areColumnsEqual(Column column1, Column column2) {
        return column1.getName().equals(column2.getName()) &&
                column1.getType().equals(column2.getType()) &&
                column1.getLength().equals(column2.getLength()) &&
                column1.isNullable() == column2.isNullable();
    }

    private Column findColumn(List<Column> columns, String columnName) {
        for (Column column : columns) {
            if (column.getName().equals(columnName)) {
                return column;
            }
        }
        return null;
    }

    public static class Column {
        private String name;
        private String type;
        private Integer length;
        private Boolean nullable;

        // Getters and setters
        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public Integer getLength() {
            return length;
        }

        public void setLength(Integer length) {
            this.length = length;
        }

        public Boolean isNullable() {
            return nullable;
        }

        public void setNullable(Boolean nullable) {
            this.nullable = nullable;
        }

        // 比较方法，用于比较两个Column对象是否相等
        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            Column column = (Column) o;
            return Objects.equals(name, column.name) &&
                    Objects.equals(type, column.type) &&
                    Objects.equals(length, column.length) &&
                    Objects.equals(nullable, column.nullable);
        }

        @Override
        public int hashCode() {
            return Objects.hash(name, type, length, nullable);
        }
    }
}
